﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SocketServer
{
    public class DBHelper 
    {
        public static DataTable GetTable(string sql)
        {
            DBConnection.connect();
            DataSet ds = new DataSet();
            MySqlDataAdapter da = new MySqlDataAdapter(sql, DBConnection._conn);
            da.Fill(ds, "table");
            DBConnection.Close();
            return ds.Tables[0];

        }

        /// <summary>
        /// 数据库中存在记录
        /// </summary>
        /// <param name="md5"></param>
        /// <returns></returns>
        public static bool IsExist(string md5)
        {
            bool isexist = false;

            try
            {
                DBConnection.connect();
                //MySqlTransaction transaction = DBConnection._conn.BeginTransaction();
                //MySqlCommand cmd = DBConnection._conn.CreateCommand();
                // cmd.Transaction = transaction;
                string sql = "select md5,importpath,exportpath from tasklist where md5=?md5 limit 1";
                MySqlCommand cmd = new MySqlCommand(sql, DBConnection._conn);


                MySqlParameter[] parameters = {
                new MySqlParameter("?md5", MySqlDbType.VarChar,16)
                };
                parameters[0].Value = md5;
                cmd.Parameters.Add(parameters[0]);

                DataSet ds = new DataSet();
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds, "tasklist");
                DataTable dt = ds.Tables[0];
                if(dt.Rows.Count==1)
                {
                    isexist = true;
                }
                //transaction.Commit();
            }
            catch (Exception ex)
            {
                Common.Log.Error(ex.Message);
            }

            DBConnection.Close();

            return isexist;
        }




        /// <summary>
        /// 添加一条任务记录
        /// </summary>
        /// <param name="md5">文件夹路径md5</param>
        /// <param name="importpath">源文件夹路径</param>
        /// <param name="exportpath">生成文件夹路径</param>
        public static void InsertFinish(string md5,string importpath, string exportpath)
        {
            DBConnection.connect();
            string sql = "insert ignore into  tasklist(md5,importpath,exportpath) values(?md5,?importpath,?exportpath)";
            MySqlCommand cmd = new MySqlCommand(sql, DBConnection._conn);

            MySqlParameter[] parameters = {
            new MySqlParameter("?md5", MySqlDbType.VarChar,16),
            new MySqlParameter("?importpath", MySqlDbType.VarChar,255),
            new MySqlParameter("?exportpath", MySqlDbType.VarChar,255)
            };

            parameters[0].Value = md5;
            parameters[1].Value = importpath;
            parameters[2].Value = exportpath;
            foreach (MySqlParameter param in parameters)
            {
                cmd.Parameters.Add(param); 
            }

            cmd.ExecuteNonQuery();

            DBConnection.Close();
        }

        public static void Delete()
        {
            MySqlCommand cmd = new MySqlCommand("create database  if not exists a", DBConnection._conn);
            cmd.ExecuteNonQuery();
        }
    }
}
